#!/use/bin/python
# coding=utf-8
# 主动服务 开机率 analysis_startup_rate
import datetime
import pymysql

from dbutils.pooled_db import PooledDB



# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 大数据 57 数据源
def getBigDataConnection():
    pool = PooledDB(pymysql, 1, host='192.168.1.57', user='proactive_read', passwd='3zmqzgw4MR',
                    db='Cboard', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


def updateFlag():
    try:
        conn, cur = getConfConnection()
        # 修改 analysis_subscribe_number 数据
        selectSql = f"""select t.active_id from life_assistant_device t 
where t.active_id not in (select tt.active_id from life_assistant_devices tt where (tt.switches_state1 & (1 << (16 - 1)))<>0) and t.zmgn_execute_flag = 1 """
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        updateSql = "update life_assistant_devices set switches_state1 = switches_state1 |(1 << (16 - 1)) where 1=1"
        delNum = 1
        dataStr = ""
        #1万条数据更新一次
        for result in selectResult:
            id = result[0]
            dataStr = f"{dataStr} '{str(id)}',"
            delNum  = delNum+1
            if delNum>10000:
                updateSql2 = f"{updateSql} and active_id in ( {dataStr[:-1]} )"
                print(updateSql2)
                cur.execute(updateSql2)
                conn.commit()
                delNum = 1
                dataStr= ""
        #剩余的数据
        updateSql2 = f"{updateSql} and active_id in ( {dataStr[:-1]} )"
        print(updateSql2)
        cur.execute(updateSql2)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def selectBigData():
    try:
        conn, cur = getBigDataConnection()
        selectSql = "select sum(active_num) AS num  FROM `ku9_kanban_device_active_pay_day` t where t.version = '9.0' and day ='2023-07-12'"
        cur.execute(selectSql)
        res = cur.fetchone()
        print("------------------ku9_kanban_device_active_pay_day", res[0], "--------------------------")
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

# 同步 data 库中 的proactive_service_device数据量
def insertData():
    try:
        conn, cur = getDataConnection()
        # 同步proactive_service_device 数据
        deleteSql = "delete from  proactive_service_device"
        insertSql = """
                    insert into proactive_service_device(active_id,mac,ip,night_vol,sleep_time,wakeup_time,is_up_log,is_scan_device,privacy_policy,create_time,last_update_time)
                    select active_id,mac,ip,night_vol,sleep_time,wakeup_time,is_up_log,is_scan_device,privacy_policy,create_time,last_update_time from life_assistant_device
                """
        cur.execute(deleteSql)
        cur.execute(insertSql)
        conn.commit()
        selectSql = "select count(1) from proactive_service_device"
        cur.execute(selectSql)
        res = cur.fetchone()
        print("------------------同步的proactive_service_device数据量", res[0], "--------------------------")
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

# 同步 conf 库中 的life_assistant_device数据量
def insertData2():
    try:
        conn, cur = getDataConnection()
        selectdeviceSql = """select active_id,mac,ip,yjyl_begin_time,yjyl_end_time,jkzx_begin_time,jkzx_end_time,zjkj_q_begin_time,zjkj_q_end_time,
                          zjkj_ui,zjkj,yjyl_ui,yjyl,jkzx_ui,jkzx,zjkj_execute_flag,yjyl_execute_flag,jkzx_execute_flag from life_assistant_device """
        cur.execute(selectdeviceSql)
        resultNum = cur.fetchall()
        conn, cur = getConfConnection()
        # 同步proactive_service_device 数据
        deleteSql = "delete from  life_assistant_device where 1=1"
        cur.execute(deleteSql)
        conn.commit()
        for result in resultNum:
            insertSql = """
                                insert into life_assistant_device(active_id,mac,ip,yjyl_begin_time,yjyl_end_time,jkzx_begin_time,jkzx_end_time,zjkj_q_begin_time,zjkj_q_end_time,
                          zjkj_ui,zjkj,yjyl_ui,yjyl,jkzx_ui,jkzx,zjkj_execute_flag,yjyl_execute_flag,jkzx_execute_flag)
                                value ('%s','%s','%s','%s','%s','%s','%s','%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s)
                            """ %(result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7],result[8],result[9],result[10],
                                  result[11],result[12],result[13],result[14],result[15],result[16],result[17])
            cur.execute(insertSql)
            conn.commit()
        selectSql = "select count(1) from life_assistant_device"
        cur.execute(selectSql)
        res = cur.fetchone()
        print("------------------同步的life_assistant_device数据量", res[0], "--------------------------")
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

# 修改 analysis_subscribe_number 数据
def analysis_subscribe_number():
    try:
        conn, cur = getDataConnection()
        # 修改 analysis_subscribe_number 数据
        deleteSql = "delete from  analysis_subscribe_number where id in (1,2,3,4)"
        updateSql1 = """update analysis_subscribe_number t set t.new_num = 5 where id =5"""
        updateSql2 = """update analysis_subscribe_number t set t.new_num = 7 where id =6"""
        updateSql3 = """update analysis_subscribe_number t set t.new_num = 9 where id =8"""
        cur.execute(deleteSql)
        cur.execute(updateSql1)
        cur.execute(updateSql2)
        cur.execute(updateSql3)
        conn.commit()
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

# 更新数据
def updateData():
    print("------------------开始执行--------------------------")
    try:
        conn, cur = getDataConnection()
        # 更新 数据
        updateSql1 = """update analysis_subscribe_number t set t.new_num = 4 where id =5"""
        updateSql2 = """update analysis_subscribe_number t set t.new_num = 4 where id =6"""
        updateSql3 = """update analysis_subscribe_number t set t.new_num = 6 where id =8"""
        cur.execute(updateSql1)
        cur.execute(updateSql2)
        cur.execute(updateSql3)
        conn.commit()
        print("------------------ 更新成功 --------------------------")
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

# 更新数据
def insertData3():
    print("------------------开始执行--------------------------")
    try:
        conn, cur = getConfConnection()
        # 更新 数据
        insertSql = """
    INSERT INTO `service_push_list`(`task_id`, `active_id`) VALUES (99, '97422564'),(99, '97459529'),(99, '97482093'),(99, '97359476'),(99, '97394882'),(99, '97189385'),(99, '97230508'),(99, '97076328'),(99, '97251480'),(99, '97257616'),(99, '97256073'),(99, '97268818'),(99, '97120591'),(99, '96855601'),(99, '97080615'),(99, '97090073'),(99, '97173986'),(99, '97179317'),(99, '96704595'),(99, '95916691'),(99, '96037999'),(99, '95940855'),(99, '95995095'),(99, '96117308'),(99, '95927045'),(99, '95996026'),(99, '95877074'),(99, '96092960'),(99, '96110815'),(99, '95917848'),(99, '95979256'),(99, '95864248'),(99, '96136978'),(99, '96206319'),(99, '96248268'),(99, '96251492'),(99, '96238979'),(99, '96238801'),(99, '95911066'),(99, '95779185'),(99, '95742002'),(99, '96025735'),(99, '95980541'),(99, '95851767'),(99, '96140430'),(99, '96293830'),(99, '96213264'),(99, '95912886'),(99, '95963390'),(99, '96028924'),(99, '95990989'),(99, '95861122'),(99, '96190623'),(99, '96159274'),(99, '96197974'),(99, '96081106'),(99, '96139590'),(99, '96187705'),(99, '95884822'),(99, '95736944'),(99, '95780398'),(99, '95894390'),(99, '95719568'),(99, '95910356'),(99, '97516324'),(99, '92650136'),(99, '96880686'),(99, '96635140'),(99, '96896601'),(99, '96908587'),(99, '96354174'),(99, '96914989'),(99, '96904472'),(99, '96918209'),(99, '96792296'),(99, '96565470'),(99, '96466137'),(99, '96328870'),(99, '96390555'),(99, '96540662'),(99, '96545287'),(99, '96720184'),(99, '96581865'),(99, '96467839'),(99, '96679508'),(99, '96070132'),(99, '96513384'),(99, '96590949'),(99, '96498633'),(99, '96428891'),(99, '96226994'),(99, '96519740'),(99, '95858776'),(99, '96587389'),(99, '96547669'),(99, '96437491'),(99, '96374793'),(99, '96637814'),(99, '96414571'),(99, '96440569'),(99, '96537744'),(99, '96651592'),(99, '96500300'),(99, '96590083'),(99, '96419702'),(99, '96666091'),(99, '96507521'),(99, '96825686'),(99, '96828778'),(99, '96839137'),(99, '95905521'),(99, '96844749'),(99, '96864335'),(99, '96327951'),(99, '96532938'),(99, '96754927'),(99, '96389024'),(99, '96521828'),(99, '96757402'),(99, '96468545'),(99, '96061286'),(99, '96677337'),(99, '96534989'),(99, '96729105'),(99, '96373360'),(99, '96773493'),(99, '96790075'),(99, '96657339'),(99, '96621635'),(99, '96045393'),(99, '96666612'),(99, '96353972'),(99, '95881723'),(99, '96365731'),(99, '96371588'),(99, '96298737'),(99, '96693746'),(99, '96480154'),(99, '96320260'),(99, '96964077'),(99, '95979499'),(99, '97012584'),(99, '97068268'),(99, '97049178'),(99, '96696981'),(99, '96647144'),(99, '95883228'),(99, '96885023'),(99, '96944257'),(99, '95987274'),(99, '96566349'),(99, '95810173'),(99, '96397424'),(99, '96042928'),(99, '96713957'),(99, '96456943'),(99, '96710095'),(99, '96439723'),(99, '95768452'),(99, '96748792'),(99, '96778413'),(99, '96399432'),(99, '96831333'),(99, '96096987'),(99, '96569898'),(99, '96764987'),(99, '95879353'),(99, '95978484'),(99, '95912895'),(99, '95938678'),(99, '95904147'),(99, '96408123'),(99, '96131016'),(99, '95822012'),(99, '95928887'),(99, '95983155'),(99, '96254825'),(99, '96070491'),(99, '96224499'),(99, '96224627'),(99, '96246300'),(99, '96276435'),(99, '96082076'),(99, '96148494'),(99, '96145585'),(99, '96277936'),(99, '96031016'),(99, '96572893'),(99, '97272938'),(99, '96974403'),(99, '96262194'),(99, '97429966'),(99, '95669715'),(99, '97483681'),(99, '97413885'),(99, '97051405'),(99, '96347483'),(99, '97119550'),(99, '95977839'),(99, '97187293'),(99, '95898113'),(99, '97680103'),(99, '97646176'),(99, '97650240'),(99, '96756570'),(99, '96425261'),(99, '96625657'),(99, '96369268'),(99, '96766541'),(99, '96761342'),(99, '96379865'),(99, '96305606'),(99, '96574391'),(99, '96778233'),(99, '96698228'),(99, '95984671'),(99, '96444909'),(99, '96720779'),(99, '96581408'),(99, '96663954'),(99, '96428966'),(99, '96740015'),(99, '96586157'),(99, '96531975'),(99, '96482004'),(99, '96416562'),(99, '96714105'),(99, '96188556'),(99, '96653040'),(99, '96343149'),(99, '96960933'),(99, '96963935'),(99, '96983898'),(99, '96585803'),(99, '96953506'),(99, '96463887'),(99, '96146045'),(99, '96457214'),(99, '96021172'),(99, '96228883'),(99, '96820322'),(99, '96040961'),(99, '96574094'),(99, '96878347'),(99, '96480998'),(99, '96933523'),(99, '97061761'),(99, '97155768'),(99, '97180841'),(99, '93784820'),(99, '97325913'),(99, '97333672'),(99, '97358465'),(99, '97422134'),(99, '97213432'),(99, '97221373'),(99, '95980185'),(99, '97206721'),(99, '97272038'),(99, '97207586'),(99, '97274562'),(99, '97268489'),(99, '92391895'),(99, '97477091'),(99, '95665224'),(99, '96278188'),(99, '96041884'),(99, '96279484'),(99, '95942631'),(99, '96063380'),(99, '96292772'),(99, '96101152'),(99, '96249036'),(99, '95994746'),(99, '95976258'),(99, '95948553'),(99, '95978799'),(99, '95778923'),(99, '96083691'),(99, '96204358'),(99, '96181687'),(99, '95905404'),(99, '96214545'),(99, '95947405'),(99, '95961715'),(99, '95857997'),(99, '95942950'),(99, '95938784'),(99, '96165775'),(99, '96202104'),(99, '95874566'),(99, '96145925'),(99, '95813996'),(99, '95991240'),(99, '96169239'),(99, '96203440'),(99, '96199539'),(99, '96072626'),(99, '95959329'),(99, '95745767'),(99, '91377707'),(99, '95810980'),(99, '95844720'),(99, '95867952'),(99, '96081628'),(99, '96204995'),(99, '96040750'),(99, '96143420'),(99, '95974075'),(99, '96248628'),(99, '96101543'),(99, '96038249'),(99, '96047018'),(99, '96009299'),(99, '96317388'),(99, '96251715'),(99, '95947367'),(99, '97418470'),(99, '97196121'),(99, '97289108'),(99, '97252864'),(99, '97519343'),(99, '97127442'),(99, '92650024'),(99, '95909930'),(99, '96400073'),(99, '95953572'),(99, '95957085'),(99, '95823999'),(99, '96179649'),(99, '95856421'),(99, '96279367'),(99, '96093635'),(99, '95960300'),(99, '96103194'),(99, '95812591'),(99, '97638770'),(99, '96629756'),(99, '96890049'),(99, '96471589'),(99, '96298359'),(99, '96531158'),(99, '96125231'),(99, '96071937'),(99, '96822837'),(99, '96765383'),(99, '95856918'),(99, '96677429'),(99, '96520617'),(99, '96337431'),(99, '96606040'),(99, '96515989'),(99, '95886533'),(99, '96386011'),(99, '96555866'),(99, '96869188'),(99, '95996959'),(99, '96092736'),(99, '95651231'),(99, '96104610'),(99, '96220352'),(99, '94103452'),(99, '97091451'),(99, '96696183'),(99, '96734296'),(99, '96678471'),(99, '96535369'),(99, '96608358'),(99, '96456304'),(99, '96976843'),(99, '96993643'),(99, '96934440'),(99, '96663544'),(99, '96624342'),(99, '95824815'),(99, '96901971'),(99, '97075355'),(99, '97139454'),(99, '97369255'),(99, '97104388'),(99, '97264217'),(99, '96072337'),(99, '95719636'),(99, '95657546'),(99, '96026558'),(99, '96086550'),(99, '96135949'),(99, '96201192'),(99, '96203248'),(99, '95686393'),(99, '96148390'),(99, '96236153'),(99, '96268547'),(99, '97356949'),(99, '97353710'),(99, '97299277'),(99, '97180935'),(99, '95994847'),(99, '96213243'),(99, '95971413'),(99, '96183106'),(99, '95958704'),(99, '96179490'),(99, '96013057'),(99, '95834101'),(99, '95897625'),(99, '96895159'),(99, '96910973'),(99, '96190313'),(99, '96055913'),(99, '96655241'),(99, '96719200'),(99, '96690691'),(99, '96391824'),(99, '96809525'),(99, '96792740'),(99, '96820744'),(99, '96430581'),(99, '96420542'),(99, '96684056'),(99, '96799543'),(99, '96453509'),(99, '96499912'),(99, '96525415'),(99, '96487961'),(99, '96981476'),(99, '97012533'),(99, '96934960'),(99, '96898548'),(99, '96415110'),(99, '96382797'),(99, '96841431'),(99, '96849595'),(99, '96758769'),(99, '95971062'),(99, '96182590'),(99, '95904194'),(99, '96202890'),(99, '96222645'),(99, '95810487'),(99, '96203371'),(99, '95794921'),(99, '96853869'),(99, '97317292'),(99, '96532194'),(99, '93705961'),(99, '94329148'),(99, '96352770'),(99, '95944482'),(99, '96626023'),(99, '96307183'),(99, '96341273'),(99, '95835795'),(99, '97115413'),(99, '97149791'),(99, '97382847'),(99, '95925989'),(99, '97264683'),(99, '95972601'),(99, '97463732'),(99, '96000751'),(99, '96095147'),(99, '96006715'),(99, '91668076'),(99, '95811997'),(99, '96236879'),(99, '95903007'),(99, '95911685'),(99, '95832828'),(99, '97274952'),(99, '93436839'),(99, '97345737'),(99, '97357383'),(99, '96563686'),(99, '97230669'),(99, '96124717'),(99, '97077208'),(99, '97315068'),(99, '95998535'),(99, '97093006'),(99, '97110196'),(99, '97173574'),(99, '97467384'),(99, '95932619'),(99, '96023857'),(99, '95877059'),(99, '96217060'),(99, '95872569'),(99, '96187526'),(99, '96074452'),(99, '96213542'),(99, '96117750'),(99, '96218160'),(99, '96078052'),(99, '95855659'),(99, '96077597'),(99, '96028796'),(99, '95876212'),(99, '96194402'),(99, '96021721'),(99, '96192368'),(99, '96288991'),(99, '96295106'),(99, '96176818'),(99, '96227455'),(99, '96028130'),(99, '96054306'),(99, '95994268'),(99, '96008065'),(99, '96159665'),(99, '95990328'),(99, '96883447'),(99, '96886400'),(99, '96006119'),(99, '96708733'),(99, '96912396'),(99, '96309247'),(99, '96951720'),(99, '96615882'),(99, '96315827'),(99, '96320075'),(99, '96564417'),(99, '96030732'),(99, '96624476'),(99, '96507897'),(99, '96609896'),(99, '96751923'),(99, '96845920'),(99, '96743958'),(99, '96681262'),(99, '96519323'),(99, '95992396'),(99, '95840556'),(99, '96235625'),(99, '96795317'),(99, '96205315'),(99, '96389650'),(99, '96310606'),(99, '96312215'),(99, '96482092'),(99, '96298590'),(99, '96415926'),(99, '96326613'),(99, '96474852'),(99, '96315488'),(99, '96723555'),(99, '96741738'),(99, '96679691'),(99, '96963867'),(99, '97012023'),(99, '95965569'),(99, '96994684'),(99, '95927942'),(99, '96087054'),(99, '96698523'),(99, '96606117'),(99, '96548232'),(99, '96675781'),(99, '96674739'),(99, '96474946'),(99, '96551585'),(99, '96374311'),(99, '96794772'),(99, '95923318'),(99, '96064258'),(99, '96036221'),(99, '96054990'),(99, '95865242'),(99, '96236449'),(99, '96278926'),(99, '96652055'),(99, '97219781'),(99, '96662150'),(99, '97414305'),(99, '97080338'),(99, '97150404'),(99, '94072902'),(99, '97651026'),(99, '95636447'),(99, '91589865'),(99, '96513700'),(99, '96425570'),(99, '96605770'),(99, '96321616'),(99, '96114464'),(99, '96335648'),(99, '96556084'),(99, '96551747'),(99, '96442310'),(99, '96428925'),(99, '96425758'),(99, '95841102'),(99, '97066361'),(99, '97066431'),(99, '96652665'),(99, '96877347'),(99, '96891447'),(99, '96849966'),(99, '96908348'),(99, '83721124'),(99, '97154642'),(99, '97338786'),(99, '97425789'),(99, '92490261'),(99, '97546671'),(99, '97398591'),(99, '97445543'),(99, '96438112'),(99, '95751678'),(99, '96041287'),(99, '95855644'),(99, '96026006'),(99, '96053459'),(99, '95768476'),(99, '95781930'),(99, '95856118'),(99, '95900547'),(99, '96074046'),(99, '95736181'),(99, '96026645'),(99, '96214459'),(99, '95801798'),(99, '96238933'),(99, '96201837'),(99, '95710463'),(99, '95978586'),(99, '96145100'),(99, '96078446'),(99, '96108049'),(99, '96193642'),(99, '97455279'),(99, '97471648'),(99, '97477542'),(99, '97207509'),(99, '97205816'),(99, '96747932'),(99, '97241047'),(99, '97243424'),(99, '95731254'),(99, '97082414'),(99, '97046533'),(99, '97175783'),(99, '95978530'),(99, '95991282'),(99, '95992472'),(99, '95978927'),(99, '95934244'),(99, '96118021'),(99, '95889079'),(99, '96191015'),(99, '96225887'),(99, '96235324'),(99, '96092179'),(99, '96233460'),(99, '95969314'),(99, '96259644'),(99, '96092351'),(99, '96070410'),(99, '96278385'),(99, '96066448'),(99, '95872387'),(99, '96214696'),(99, '95850171'),(99, '95931288'),(99, '96199462'),(99, '96042387'),(99, '96213133'),(99, '96200702'),(99, '95970846'),(99, '96161976'),(99, '95975337'),(99, '95958014'),(99, '97648988'),(99, '97588738'),(99, '97650411'),(99, '96926935'),(99, '96517178'),(99, '96937647'),(99, '96608495'),(99, '96540198'),(99, '96539311'),(99, '96538648'),(99, '96427807'),(99, '96356876'),(99, '96422014'),(99, '96493178'),(99, '96432656'),(99, '96825931'),(99, '96502459'),(99, '96865797'),(99, '96747265'),(99, '96745726'),(99, '96756669'),(99, '96782545'),(99, '96450678'),(99, '96681737'),(99, '96612477'),(99, '96677515'),(99, '96661955'),(99, '96300040'),(99, '96412722'),(99, '96489226'),(99, '96661982'),(99, '96664367'),(99, '96488239'),(99, '96625043'),(99, '96742556'),(99, '96744908'),(99, '96428581'),(99, '96982402'),(99, '97017304'),(99, '97028978'),(99, '97051353'),(99, '97065195'),(99, '96963248'),(99, '97033417'),(99, '96360166'),(99, '96421278'),(99, '96587382'),(99, '96875996'),(99, '95908342'),(99, '96910071'),(99, '96659505'),(99, '96511203'),(99, '96325397'),(99, '95971282'),(99, '96499183'),(99, '95729186'),(99, '96369280'),(99, '96618740'),(99, '96664656'),(99, '95695650'),(99, '96090437'),(99, '96093517'),(99, '96427350'),(99, '96158237'),(99, '95974872'),(99, '95772831'),(99, '96222313'),(99, '95679456'),(99, '95963374'),(99, '95787341'),(99, '96091084'),(99, '96280302'),(99, '96282336'),(99, '96296243'),(99, '97454208'),(99, '97080134'),(99, '97162464'),(99, '95770620'),(99, '97336490'),(99, '89649857'),(99, '95865932'),(99, '96750657'),(99, '96765371'),(99, '96460274'),(99, '96308345'),(99, '96391711'),(99, '96325616'),(99, '96404916'),(99, '96718200'),(99, '96666052'),(99, '96735192'),(99, '96679638'),(99, '96746007'),(99, '96304301'),(99, '96468937'),(99, '93808653'),(99, '96533298'),(99, '95781077'),(99, '96579557'),(99, '96959388'),(99, '96969846'),(99, '95679498'),(99, '96975655'),(99, '96998550'),(99, '95883761'),(99, '96751314'),(99, '96694082'),(99, '96237857'),(99, '96861098'),(99, '96757607'),(99, '96873947'),(99, '96891967'),(99, '96905305'),(99, '96908916'),(99, '96363460'),(99, '96932884'),(99, '97079592'),(99, '97092568'),(99, '96693344'),(99, '97124333'),(99, '97103148'),(99, '97164898'),(99, '97165930'),(99, '97578440'),(99, '97211777'),(99, '97228797'),(99, '95878807'),(99, '97265798'),(99, '97296952'),(99, '97308284'),(99, '96654069'),(99, '95985593'),(99, '96014691'),(99, '95844645'),(99, '95734096'),(99, '95996308'),(99, '96156444'),(99, '95777042'),(99, '96156316'),(99, '95886261'),(99, '96257453'),(99, '96191801'),(99, '96233079'),(99, '95925825'),(99, '95800684'),(99, '95707545'),(99, '96238842'),(99, '96139052'),(99, '95910085'),(99, '97383120'),(99, '97255786'),(99, '97230748'),(99, '97299670'),(99, '97091526'),(99, '95983333'),(99, '97100086'),(99, '97100776'),(99, '97094284'),(99, '96894556'),(99, '97132288'),(99, '96068884'),(99, '92171987'),(99, '96278755'),(99, '95845667'),(99, '95943287'),(99, '96137339'),(99, '95992999'),(99, '95924446'),(99, '95962544'),(99, '96254074'),(99, '96017751'),(99, '96201509'),(99, '96252209'),(99, '95716685'),(99, '96255378'),(99, '95939372'),(99, '96002912'),(99, '96131818'),(99, '95770118'),(99, '96002093'),(99, '95768898'),(99, '95742384'),(99, '95734498'),(99, '95879918'),(99, '91087487'),(99, '96859427'),(99, '96897031'),(99, '96552310'),(99, '96904636'),(99, '96391612'),(99, '96036388'),(99, '96938090'),(99, '96323004'),(99, '96521646'),(99, '96540370'),(99, '96035842'),(99, '96647821'),(99, '96618339'),(99, '96623126'),(99, '96435395'),(99, '96499199'),(99, '96644657'),(99, '96805303'),(99, '96660284'),(99, '96788320'),(99, '96550688'),(99, '96847807'),(99, '96869270'),(99, '96747722'),(99, '96494205'),(99, '96552199'),(99, '96611498'),(99, '96496979'),(99, '96776872'),(99, '96660376'),(99, '96793013'),(99, '96644838'),(99, '95867197'),(99, '96545541'),(99, '96443199'),(99, '96398241'),(99, '96426696'),(99, '96668685'),(99, '96691700'),(99, '96709305'),(99, '95866612'),(99, '96632678'),(99, '96459332'),(99, '96676146'),(99, '95776855'),(99, '96980160'),(99, '96976847'),(99, '96988022'),(99, '95954917'),(99, '96498843'),(99, '96539244'),(99, '96547225'),(99, '96419418'),(99, '96542878'),(99, '96541214'),(99, '96507279'),(99, '96493735'),(99, '96558788'),(99, '96786445'),(99, '96711251'),(99, '96127137'),(99, '96616565'),(99, '95835314'),(99, '96472352'),(99, '96202347'),(99, '96571655'),(99, '97347769'),(99, '96933255'),(99, '94163690'),(99, '97628398'),(99, '92875635'),(99, '96490740'),(99, '96752908'),(99, '96747906'),(99, '96545213'),(99, '96301036'),(99, '96594656'),(99, '96796640'),(99, '96670984'),(99, '96489517'),(99, '96552438'),(99, '96502944'),(99, '96694550'),(99, '96574486'),(99, '96417395'),(99, '96655200'),(99, '96323007'),(99, '95847115'),(99, '96664970'),(99, '96007201'),(99, '96577529'),(99, '96529763'),(99, '96936629'),(99, '96990829'),(99, '96992073'),(99, '96977653'),(99, '96407878'),(99, '96592069'),(99, '96830078'),(99, '96911702'),(99, '96896056'),(99, '96518934'),(99, '96933557'),(99, '96923113'),(99, '97100679'),(99, '97092287'),(99, '97111381'),(99, '97022191'),(99, '97392378'),(99, '97211591'),(99, '97285158'),(99, '97186336'),(99, '97308259'),(99, '95824833'),(99, '97424195'),(99, '96277329'),(99, '96018446'),(99, '96139682'),(99, '95691465'),(99, '96192047'),(99, '95802384'),(99, '95918282'),(99, '95798434'),(99, '96074159'),(99, '96111721'),(99, '95936337'),(99, '96048173'),(99, '95977777'),(99, '95904753'),(99, '96199666'),(99, '95709480'),(99, '96102128'),(99, '90856086'),(99, '95853102'),(99, '95939188'),(99, '96016834'),(99, '95840821'),(99, '95986008'),(99, '96261562'),(99, '96071274'),(99, '96118412'),(99, '96162493'),(99, '96404196'),(99, '97263791'),(99, '97375808'),(99, '96452742'),(99, '97205875'),(99, '97210582'),(99, '97192605'),(99, '96206102'),(99, '97220019'),(99, '97288903'),(99, '96080794'),(99, '96979714'),(99, '97079369'),(99, '95733652'),(99, '97107456'),(99, '97101592'),(99, '96803038'),(99, '96294241'),(99, '95992046'),(99, '95991457'),(99, '96130876'),(99, '95697242'),(99, '95864812'),(99, '96197313'),(99, '95959805'),(99, '96048457'),(99, '96260280'),(99, '96190839'),(99, '96149498'),(99, '96161987'),(99, '96248940'),(99, '95867003'),(99, '96028969'),(99, '96223793'),(99, '95977434'),(99, '96295390'),(99, '95977779'),(99, '96033537'),(99, '95755152'),(99, '96102439'),(99, '96204831'),(99, '92411311'),(99, '96877690'),(99, '96881103'),(99, '96915687'),(99, '96266449'),(99, '96049412'),(99, '96877433'),(99, '96945162'),(99, '96491187'),(99, '96958285'),(99, '96554513'),(99, '96300015'),(99, '96474234'),(99, '96720633'),(99, '96719241'),(99, '96512836'),(99, '96510626'),(99, '96592715'),(99, '96593679'),(99, '96646400'),(99, '96634661'),(99, '96370509'),(99, '96468030'),(99, '96686169'),(99, '96592269'),(99, '96849236'),(99, '96407467'),(99, '96587113'),(99, '96770596'),(99, '96772398'),(99, '96684817'),(99, '96415363'),(99, '96697465'),(99, '96508123'),(99, '96019848'),(99, '96733651'),(99, '95861925'),(99, '96961398'),(99, '96989178'),(99, '95709477'),(99, '97009714'),(99, '97034473'),(99, '96671097'),(99, '97006000'),(99, '97012218'),(99, '97008974'),(99, '97042507'),(99, '96386684'),(99, '96921901'),(99, '96952408'),(99, '96068781'),(99, '96715296'),(99, '96399768'),(99, '96027402'),(99, '96136775'),(99, '96203037'),(99, '96107296'),(99, '96040227'),(99, '96215261'),(99, '96262646'),(99, '95641218'),(99, '95934383'),(99, '95840936'),(99, '96116887'),(99, '97220442'),(99, '97077870'),(99, '97661479'),(99, '97068912'),(99, '96616445'),(99, '93147870'),(99, '92744306'),(99, '93729107'),(99, '96403032'),(99, '96089940'),(99, '96735432'),(99, '96571230'),(99, '95736702'),(99, '96336831'),(99, '96634136'),(99, '96533225'),(99, '96509681'),(99, '96545934'),(99, '95782433'),(99, '96467239'),(99, '96438094'),(99, '96451854'),(99, '96401758'),(99, '96314727'),(99, '96623185'),(99, '96519259'),(99, '96408027'),(99, '96975971'),(99, '96980896'),(99, '96981322'),(99, '96144108'),(99, '97055507'),(99, '96606428'),(99, '96759483'),(99, '96830061'),(99, '96859087'),(99, '96905567'),(99, '96925548'),(99, '97077021'),(99, '96526234'),(99, '92975417'),(99, '97388519'),(99, '97268717'),(99, '96455928'),(99, '95715437'),(99, '97354844'),(99, '97436929'),(99, '95861204'),(99, '95930247'),(99, '95781837'),(99, '96018242'),(99, '96151466'),(99, '95986894'),(99, '95991418'),(99, '95996052'),(99, '95758758'),(99, '95935401'),(99, '95741257'),(99, '95773246'),(99, '95868407'),(99, '95896038'),(99, '95693200'),(99, '95695709'),(99, '95720059'),(99, '96119187'),(99, '96243670'),(99, '96108023'),(99, '95820064'),(99, '95998447'),(99, '96277419'),(99, '96511657'),(99, '97197469'),(99, '97273386'),(99, '96426807'),(99, '97082375'),(99, '97098911'),(99, '97083677'),(99, '97095955'),(99, '97180239'),(99, '96182230'),(99, '96146154'),(99, '95928837'),(99, '95813885'),(99, '95814659'),(99, '95844416'),(99, '95793679'),(99, '95836285'),(99, '96033803'),(99, '95902439'),(99, '95780394'),(99, '96118484'),(99, '95924766'),(99, '95780873'),(99, '96155415'),(99, '96264103'),(99, '95860828'),(99, '96267763'),(99, '96015874'),(99, '96120066'),(99, '96280745'),(99, '96005873'),(99, '95836958'),(99, '96351343'),(99, '96149035'),(99, '96172427'),(99, '95751481'),(99, '96029683'),(99, '95872751'),(99, '96086224'),(99, '96878171'),(99, '96889694'),(99, '96897324'),(99, '96606835'),(99, '96508478'),(99, '96311344'),(99, '95799686'),(99, '96705668'),(99, '96563570'),(99, '96581614'),(99, '96647512'),(99, '96508453'),(99, '96498752'),(99, '96493729'),(99, '96347437'),(99, '96420118'),(99, '96450850'),(99, '95723834'),(99, '96630768'),(99, '96623964'),(99, '96380083'),(99, '96446562'),(99, '96046641'),(99, '96406423'),(99, '96819808'),(99, '96763615'),(99, '96855505'),(99, '96666008'),(99, '96567965'),(99, '96351133'),(99, '96301547'),(99, '96680317'),(99, '96661422'),(99, '96550456'),(99, '96765639'),(99, '96708746'),(99, '96563301'),(99, '96677231'),(99, '96714407'),(99, '96401747'),(99, '96484088'),(99, '96698385'),(99, '96564032'),(99, '96358097'),(99, '95735883'),(99, '96349151'),(99, '96511481'),(99, '96969064'),(99, '96989809'),(99, '97020779'),(99, '97067556'),(99, '96965992'),(99, '97040035'),(99, '96698007'),(99, '95862208'),(99, '96555177'),(99, '96759963'),(99, '96107471'),(99, '96004735'),(99, '95815538'),(99, '96093563'),(99, '95765836'),(99, '95890091'),(99, '95892463'),(99, '95765929'),(99, '96114580'),(99, '96089939'),(99, '96175397'),(99, '95975372'),(99, '95872655'),(99, '96225448'),(99, '97352531'),(99, '97415109'),(99, '97100770'),(99, '97577727'),(99, '95479834'),(99, '87542508'),(99, '96695670'),(99, '96665905'),(99, '96491083'),(99, '96012316'),(99, '96402089'),(99, '96351042'),(99, '96783184'),(99, '96514709'),(99, '96690866'),(99, '96546855'),(99, '96662943'),(99, '96734588'),(99, '96708125'),(99, '96550698'),(99, '96441118'),(99, '96340558'),(99, '96581165'),(99, '96474187'),(99, '96547973'),(99, '96664196'),(99, '96576858'),(99, '96488289'),(99, '95919333'),(99, '96444399'),(99, '96937114'),(99, '96990795'),(99, '96974990'),(99, '96930132'),(99, '96077525'),(99, '96852601'),(99, '96797560'),(99, '96896804'),(99, '96612486'),(99, '96682311'),(99, '97099697'),(99, '97128418'),(99, '97168237'),(99, '97184772'),(99, '92704127'),(99, '97271339'),(99, '97350123'),(99, '97424795'),(99, '97180587'),(99, '97209471'),(99, '97282227'),(99, '97612003'),(99, '90355304'),(99, '95403472'),(99, '97507382'),(99, '97459599'),(99, '95879762'),(99, '95830993'),(99, '95987221'),(99, '95810879'),(99, '90665668'),(99, '95772949'),(99, '95881842'),(99, '95872007'),(99, '96252828'),(99, '96224058'),(99, '96059109'),(99, '97439194'),(99, '97328136'),(99, '97360149'),(99, '97367440'),(99, '97394979'),(99, '97425269'),(99, '95593280'),(99, '97210982'),(99, '97214503'),(99, '97235305'),(99, '97241708'),(99, '97549007'),(99, '96923831'),(99, '97106256'),(99, '97160847'),(99, '97187111'),(99, '97582456'),(99, '97597436'),(99, '96052046'),(99, '95991432'),(99, '96190897'),(99, '95962662'),(99, '95976182'),(99, '95981357'),(99, '96092643'),(99, '96154969'),(99, '96122310'),(99, '95791923'),(99, '95992100'),(99, '95789019'),(99, '96100815'),(99, '95894460'),(99, '95850227'),(99, '96228621'),(99, '96140295'),(99, '95882907'),(99, '96191948'),(99, '96006055'),(99, '95971865'),(99, '95869361'),(99, '96093363'),(99, '96265056'),(99, '96250844'),(99, '95944671'),(99, '96101227'),(99, '96252002'),(99, '96274968'),(99, '96178496'),(99, '95803614'),(99, '96134926'),(99, '96203361'),(99, '96344020'),(99, '96277818'),(99, '96214285'),(99, '96078933'),(99, '96030216'),(99, '96158610'),(99, '95857978'),(99, '95941159'),(99, '96213590'),(99, '96012469'),(99, '95982197'),(99, '95830925'),(99, '95729127'),(99, '95710473'),(99, '96862006'),(99, '96890946'),(99, '96884232'),(99, '96363387'),(99, '96931721'),(99, '96939881'),(99, '96889474'),(99, '96482237'),(99, '96310150'),(99, '96475590'),(99, '96643585'),(99, '96513712'),(99, '96339235'),(99, '96579987'),(99, '96573412'),(99, '96406674'),(99, '96588812'),(99, '96459441'),(99, '95740836'),(99, '96667141'),(99, '96508212'),(99, '96511302'),(99, '96315155'),(99, '96623432'),(99, '96552446'),(99, '96379251'),(99, '96480081'),(99, '96508042'),(99, '96468552'),(99, '96353691'),(99, '96332854'),(99, '96604070'),(99, '96459211'),(99, '96807071'),(99, '96808402'),(99, '96809118'),(99, '96726599'),(99, '96644418'),(99, '96836083'),(99, '96499531'),(99, '95902592'),(99, '96855466'),(99, '96856949'),(99, '96862466'),(99, '96559527'),(99, '96401235'),(99, '96354349'),(99, '96616705'),(99, '96778886'),(99, '96517811'),(99, '96792645'),(99, '96622714'),(99, '96793144'),(99, '96473647'),(99, '96535333'),(99, '96694225'),(99, '96425204'),(99, '96421442'),(99, '96376328'),(99, '96582987'),(99, '96685339'),(99, '96570233'),(99, '96956881'),(99, '96960380'),(99, '96969342'),(99, '96977828'),(99, '97020962'),(99, '97046477'),(99, '95589139'),(99, '97014893'),(99, '96313549'),(99, '96647733'),(99, '95791025'),(99, '96050620'),(99, '96584191'),(99, '96601745'),(99, '96708456'),(99, '96711012'),(99, '96439044'),(99, '96109474'),(99, '96608278'),(99, '96455438'),(99, '96674977'),(99, '96477970'),(99, '96418162'),(99, '96551642'),(99, '96354145'),(99, '96579833'),(99, '96013226'),(99, '96328032'),(99, '96777717'),(99, '96095155'),(99, '95769300'),(99, '95694807'),(99, '95962295'),(99, '96095029'),(99, '95987810'),(99, '96093919'),(99, '96038790'),(99, '96067345'),(99, '95953065'),(99, '96035370'),(99, '96083747'),(99, '96201105'),(99, '96228309'),(99, '96028069'),(99, '96017164'),(99, '95948044'),(99, '97069261'),(99, '97140509'),(99, '95877591'),(99, '96980821'),(99, '96761109'),(99, '97412134'),(99, '96277903'),(99, '97133629'),(99, '97165933'),(99, '96676864'),(99, '97646175'),(99, '92489443'),(99, '97653622'),(99, '97051739'),(99, '96530944'),(99, '96665728'),(99, '96767550'),(99, '96659408'),(99, '95867839'),(99, '96797586'),(99, '96368339'),(99, '96607947'),(99, '96472647'),(99, '96453010'),(99, '96709820'),(99, '96587314'),(99, '96578336'),(99, '96731924'),(99, '96470618'),(99, '96639645'),(99, '96732520'),(99, '96677851'),(99, '95808250'),(99, '96424689'),(99, '96479681'),(99, '96734125'),(99, '96515623'),(99, '96489178'),(99, '96645668'),(99, '96399336'),(99, '96599099'),(99, '96586074'),(99, '96325332'),(99, '96069612'),(99, '96619978'),(99, '96429609'),(99, '96484326'),(99, '95942847'),(99, '96641290'),(99, '96585902'),(99, '96642252'),(99, '96370924'),(99, '96967628'),(99, '96983995'),(99, '96569068'),(99, '96704459'),(99, '96619248'),(99, '96583075'),(99, '96880854'),(99, '96885941'),(99, '96886113'),(99, '96847371'),(99, '96922412'),(99, '96472728'),(99, '96122957'),(99, '97078506'),(99, '97134754'),(99, '97136125'),(99, '97397385'),(99, '97366528'),(99, '97108785'),(99, '97409485'),(99, '97422691'),(99, '97410538'),(99, '97051292'),(99, '96861176'),(99, '97268711'),(99, '96045165'),(99, '97270287'),(99, '97263392'),(99, '97306881'),(99, '89281753'),(99, '97458856'),(99, '96144317'),(99, '94186632'),(99, '90173725'),(99, '97488643'),(99, '97352306'),(99, '97368793'),(99, '97440086'),(99, '96662853'),(99, '97406101'),(99, '96278459'),(99, '95832621'),(99, '95723718'),(99, '96134861'),(99, '96186385'),(99, '95946629'),(99, '96108082'),(99, '95963440'),(99, '95886896'),(99, '95854121'),(99, '96039634'),(99, '96081775'),(99, '95912875'),(99, '96070556'),(99, '95810939'),(99, '96082550'),(99, '96093074'),(99, '96150894'),(99, '96116077'),(99, '96121896'),(99, '95946934'),(99, '95675776'),(99, '96194123'),(99, '96076250'),(99, '96250570'),(99, '96202960'),(99, '95959778'),(99, '96018586'),(99, '95953502'),(99, '95904327'),(99, '97469012'),(99, '97175500'),(99, '97221350'),(99, '97295810'),(99, '96791574'),(99, '95776490'),(99, '96216122'),(99, '96034076'),(99, '96166260'),(99, '95968628'),(99, '96078153'),(99, '96176998'),(99, '95963267'),(99, '96051220'),(99, '95904243'),(99, '95981931'),(99, '96220768'),(99, '96172207'),(99, '96032354'),(99, '96225357'),(99, '96266588'),(99, '96062566'),(99, '95732207'),(99, '96071465'),(99, '96186361'),(99, '96178935'),(99, '95877519'),(99, '95819693'),(99, '96756840'),(99, '96892377'),(99, '96892646'),(99, '96928191'),(99, '96522293'),(99, '96651196'),(99, '96582368'),(99, '96555218'),(99, '96321153'),(99, '96722454'),(99, '96722972'),(99, '96533864'),(99, '96325586'),(99, '96800067'),(99, '96156186'),(99, '96781088'),(99, '96457325'),(99, '96680308'),(99, '96758572'),(99, '96319307'),(99, '96569685'),(99, '96521508'),(99, '96335313'),(99, '96496288'),(99, '96569247'),(99, '96449791'),(99, '96350541'),(99, '96719916'),(99, '96729619'),(99, '96694740'),(99, '96072395'),(99, '96494009'),(99, '96324069'),(99, '95853707'),(99, '96965138'),(99, '96414046'),(99, '96810344'),(99, '96903291'),(99, '96914286'),(99, '96938982'),(99, '96836650'),(99, '96708267'),(99, '96585641'),(99, '96838469'),(99, '96762017'),(99, '96534676'),(99, '96798659'),(99, '96003446'),(99, '95994235'),(99, '95792322'),(99, '96182864'),(99, '95892613'),(99, '95866493'),(99, '96199739'),(99, '96063115'),(99, '96269067'),(99, '96272787'),(99, '96289302'),(99, '96530021'),(99, '97547262'),(99, '87112714'),(99, '90125127'),(99, '97462046'),(99, '94286092'),(99, '96767365'),(99, '96735627'),(99, '96364730'),(99, '96357189'),(99, '96353775'),(99, '96303612'),(99, '96540633'),(99, '96342126'),(99, '96635138'),(99, '97025911'),(99, '96368738'),(99, '96844688'),(99, '96849845'),(99, '96784676'),(99, '96909163'),(99, '96920142'),(99, '96926612'),(99, '96455346'),(99, '96816430'),(99, '96958064'),(99, '97049892'),(99, '96994897'),(99, '96619065'),(99, '97145769'),(99, '97365997'),(99, '97395995'),(99, '97415242'),(99, '96133358'),(99, '97276129'),(99, '97479936'),(99, '95874010'),(99, '96031402'),(99, '96127745'),(99, '95799425'),(99, '96127421'),(99, '96148341'),(99, '95976299'),(99, '96080013'),(99, '96059407'),(99, '95943324'),(99, '95926540'),(99, '95770712'),(99, '96385719'),(99, '96567416'),(99, '97194064'),(99, '96227433'),(99, '97074355'),(99, '95944518'),(99, '96054515'),(99, '96012924'),(99, '95975262'),(99, '95960985'),(99, '95894174'),(99, '95850996'),(99, '95824862'),(99, '96112633'),(99, '95893561'),(99, '95908435'),(99, '96289961'),(99, '96291171'),(99, '95927128'),(99, '95927442'),(99, '96893518'),(99, '96890281'),(99, '96487363'),(99, '96501752'),(99, '95899831'),(99, '96546034'),(99, '96802005'),(99, '96456525'),(99, '96455592'),(99, '96760928'),(99, '96795173'),(99, '96002638'),(99, '96695524'),(99, '96568427'),(99, '96735216'),(99, '96657920'),(99, '96506417'),(99, '96736904'),(99, '96647218'),(99, '96962150'),(99, '96969427'),(99, '96969353'),(99, '96480003'),(99, '97060441'),(99, '96534179'),(99, '96121565'),(99, '96359271'),(99, '96406559'),(99, '96483625'),(99, '96731377'),(99, '96589658'),(99, '96764079'),(99, '93852035'),(99, '96355822'),(99, '95894569'),(99, '96050256'),(99, '95931532'),(99, '95928695'),(99, '95940061'),(99, '96153321'),(99, '96048208'),(99, '97232011'),(99, '97634097'),(99, '89769166'),(99, '94252835'),(99, '90661455'),(99, '96302515'),(99, '96135621'),(99, '96582983'),(99, '96769438'),(99, '96787889'),(99, '96687941'),(99, '96522559'),(99, '96465146'),(99, '96962977'),(99, '96975988'),(99, '97039467'),(99, '97062424'),(99, '96781317'),(99, '96839829'),(99, '96471935'),(99, '93435634'),(99, '97343988'),(99, '97394006'),(99, '96899080'),(99, '97284144'),(99, '93944234'),(99, '89520406'),(99, '97486114'),(99, '96060639'),(99, '95891867'),(99, '96137841'),(99, '95977655'),(99, '95927137'),(99, '96074278'),(99, '96170378'),(99, '96136791'),(99, '96053141'),(99, '96103333'),(99, '95977781'),(99, '97351992'),(99, '97284506'),(99, '97271409'),(99, '97291703'),(99, '97319202'),(99, '97123391'),(99, '97124215'),(99, '97124194'),(99, '97136052'),(99, '96274654'),(99, '96500329'),(99, '95772921'),(99, '95897079'),(99, '96030289'),(99, '95940301'),(99, '95882643'),(99, '96160765'),(99, '96169846'),(99, '96228045'),(99, '96246720'),(99, '96240448'),(99, '96020280'),(99, '96255663'),(99, '96097407'),(99, '95904014'),(99, '96095610'),(99, '96168201'),(99, '95913237'),(99, '96294578'),(99, '95629776'),(99, '96169781'),(99, '96279687'),(99, '96083581'),(99, '95925326'),(99, '96170887'),(99, '96005636'),(99, '95839952'),(99, '96846716'),(99, '96477648'),(99, '96934309'),(99, '96353373'),(99, '95867578'),(99, '96093323'),(99, '96396057'),(99, '96407141'),(99, '95986659'),(99, '96721218'),(99, '96325337'),(99, '96570833'),(99, '96814273'),(99, '96819933'),(99, '96825053'),(99, '96814071'),(99, '96850850'),(99, '96476288'),(99, '96755631'),(99, '96682365'),(99, '96783032'),(99, '96763491'),(99, '95959610'),(99, '96699809'),(99, '96576079'),(99, '96337331'),(99, '96732085'),(99, '96523672'),(99, '96648960'),(99, '96743907'),(99, '97026232'),(99, '97044977'),(99, '96380946'),(99, '96874901'),(99, '96347013'),(99, '96467489'),(99, '96249354'),(99, '96469175'),(99, '96365451'),(99, '96624871'),(99, '96560738'),(99, '96309698'),(99, '96661798'),(99, '96693641'),(99, '95915364'),(99, '96428106'),(99, '96050450'),(99, '96001868'),(99, '95721781'),(99, '96257099'),(99, '96245731'),(99, '97376862'),(99, '96499259'),(99, '97385282'),(99, '95426042'),(99, '97079195'),(99, '90693203'),(99, '96579214'),(99, '96396066'),(99, '96794936'),(99, '96430953'),(99, '96589988'),(99, '96724510'),(99, '96473725'),(99, '96387513'),(99, '96304984'),(99, '96395719'),(99, '96400490'),(99, '96481058'),(99, '96684678'),(99, '96571065'),(99, '96040988'),(99, '96981794'),(99, '96974765'),(99, '97008666'),(99, '96485189'),(99, '96922638'),(99, '96908851'),(99, '97084883'),(99, '97406622'),(99, '96696322'),(99, '95854358'),(99, '97241003'),(99, '97014704'),(99, '97264271'),(99, '96044242'),(99, '91181826'),(99, '91460387'),(99, '92264748'),(99, '91183070'),(99, '93364492'),(99, '97450100'),(99, '95787779'),(99, '96037238'),(99, '95882087'),(99, '95999488'),(99, '95986218'),(99, '96057623'),(99, '96218515'),(99, '96126145'),(99, '96177689'),(99, '90638333'),(99, '96251996'),(99, '96258777'),(99, '96041142'),(99, '96142442'),(99, '96228568'),(99, '96272029');

    """
        cur.execute(insertSql)
        conn.commit()
        print("------------------ 更新成功 --------------------------")
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

# 初始化 设备 zjkj_ask_num 数据
def zjkj_ask_num():
    try:
        conn, cur = getDataConnection()
        conn1, cur1 = getConfConnection()
        # 修改 analysis_subscribe_number 数据
        selectSql = "select DISTINCT(t.active_id) from log_zjkj_yl t where t.result = 1"
        updateSql1 = "update life_assistant_device t  set t.zjkj_ask_num = 1 where t.zjkj_ask_num = 0 "
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        dateStr = "and t.active_id in ("
        for result in selectResult:
            activeId = result[0];
            dateStr = dateStr + """'""" + activeId + """',"""
        dateStr = dateStr[:-1] + ")"
        #print("激活id过滤条件:%s" % dateStr)
        print(updateSql1+dateStr)
        cur1.execute(updateSql1+dateStr)
        conn1.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def exportMac():
    try:
        conn, cur = getConfConnection()
        # 修改 analysis_subscribe_number 数据
        selectSql = "select t.mac,t.zjkj_ask_num from life_assistant_device t where t.zjkj_ui = 1 and t.zjkj = 0 order by t.zjkj_ask_num desc limit 10000"
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        for result in selectResult:
            mac = result[0]
            zjkjAskNum = result[1]
            print(f"{mac} {zjkjAskNum}")
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 获取 订阅早间开机并且有起床经验值的数量
def zjkj_sub_num():
    try:
        conn, cur = getConfConnection()
        conn1, cur1 = getDataConnection()
        # 修改 analysis_subscribe_number 数据
        selectSqlConf = "select t.active_id from life_assistant_device t where t.zjkj = 1 and t.zjkj_ui =1"
        selectSqlData = "select count(1) from proactive_service_device  tt where (tt.wakeup_time is not null or tt.prediction_wakeup_time is not null)  "
        cur.execute(selectSqlConf)
        selectResult = cur.fetchall()
        dateStr = "and tt.active_id in ("
        for result in selectResult:
            activeId = result[0];
            dateStr = dateStr + """'""" + activeId + """',"""
        dateStr = dateStr[:-1] + ")"
        print(selectSqlData + dateStr)
        cur1.execute(selectSqlData + dateStr)
        numResult = cur1.fetchone()
        num = 0
        if numResult is not None:
            num = numResult[0]
        print(f"订阅早间开机并且有起床经验值的数量:{num}")
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 将生活助手库中。订阅早间服务的激活id 插入主动服务库
def save_zjkj_sub():
    try:
        conn, cur = getConfConnection()
        conn1, cur1 = getDataConnection()
        # 先删除
        deleteSql = "delete from device_subscribe_zjkj where 1=1";
        cur1.execute(deleteSql)
        conn1.commit()
        print('---------------- 删除device_subscribe_zjkj成功  -------------------')
        #查出已订阅的激活id 数据
        selectSqlConf = "select t.active_id,t.mac from life_assistant_device t where t.zjkj = 1 and t.zjkj_ui =1"
        cur.execute(selectSqlConf)
        selectResult = cur.fetchall()
        num =0
        for result in selectResult:
            activeId = result[0];
            mac = result[0];
            insertSql = f"insert into device_subscribe_zjkj(active_id,mac) values ('{activeId}','{mac}')"
            cur1.execute(insertSql)
            conn1.commit()
            num = num+1
        print(f'-------------- 插入 device_subscribe_zjkj数量为{num}----------------')
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def exportZmgnUser():
    try:
        conn, cur = getDataConnection()
        # 导出助眠功能用户使用情况
        selectSql = "select ifnull(t.active_id,'null') active_id,t.use_num,t.total_use_time,t.avg_use_time,t.most_use_time from analysis_zmgn_user t"
        cur.execute(selectSql)
        selectResult = cur.fetchall()
        for result in selectResult:
            activeId = result[0]
            useNum = result[1]
            totalUseTime = result[2]
            avgUseTime = result[3]
            mostUseTime = result[4]
            print(f"{activeId},{useNum},{totalUseTime},{avgUseTime},{mostUseTime}")
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def insertData5():
    try:
        conn, cur = getConfConnection()
        #
        insertSql = """
			INSERT INTO `service_device_tag`( `tag_name`, `type`, `content`, `status`, `create_time`, `last_update_time`) 
          VALUES ('激活id为24186028', '1', 'select t.active_id from life_assistant_device t where t.active_id = \'24186028\'', 1, '2023-06-06 11:09:28', NULL)               """
        cur.execute(insertSql)
        conn.commit()
        selectSql = "select count(1) from service_device_tag t"
        cur.execute(selectSql)
        res = cur.fetchone()
        print("------------------同步的 service_device_tag 数据量", res[0], "--------------------------")
    except Exception as e:
        print(e)

    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #insertData3()
    #insertData()
    #insertData2()
    #analysis_subscribe_number()
    #save_zjkj_sub()
    #selectBigData()
    #updateFlag()
    #exportZmgnUser()
    #insertData5()
    selectBigData()